USE [dbmigration]
GO
/****** Object:  StoredProcedure [dbo].[sp_record150]    Script Date: 07/15/2013 23:42:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[sp_record150]
	-- Add the parameters for the stored procedure here
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
-- Get process id from the tx_reference_values table

DECLARE @Output Integer;
set @Output = (SELECT column_value FROM tx_reference_values where record_type = 'tx_process_info' and column_name = 'process_id');
--print @Output;
	
--Select * From [disys-dev01].[PeopleSoft].[dbo].PS_LOCATION_TBL
--delete from [dbmigration].[dbo].[tx_150ERTICSWKSITE] where finalized=0;
INSERT INTO [dbmigration].[dbo].[tx_150ERTICSWKSITE](finalized,process_id,ticsworksitelocnumber,ticsworksitelocname,ticsworksitelocaddr_1,ticsworksitelocaddr_2,ticsworksiteloccity_1,ticsworksitelocstate_1,ticsworksiteloczip_1,ticsworksiteloccountry_1,ticsworksitephyscnty)
-- Select ISO2 country code from country table based on the ISO3 code from the existing data base.
SELECT 0,@Output,A.CUST_ID+'_'+CAST(A.ADDRESS_SEQ_NUM AS varchar(12)) ,CASE S.DESCR WHEN '' THEN 'NA' ELSE S.DESCR end,CASE A.ADDRESS1 WHEN '' THEN 'NA' ELSE SUBSTRING(A.ADDRESS1,0,31) end,SUBSTRING(A.ADDRESS2,0,31),CASE A.CITY WHEN '' THEN 'NA' ELSE A.CITY end,CASE A.STATE WHEN '' THEN 'NA' ELSE A.STATE end,CASE A.STATE WHEN '' THEN 'NA' ELSE A.POSTAL end,SUBSTRING(A.COUNTRY,0,3),'' FROM [disys-dev01].[PeopleSoft].[dbo].PS_CUST_ADDRESS A, [disys-dev01].[PeopleSoft].[dbo].PS_CUST_ADDR_SEQ S WHERE A.CUST_ID = S.CUST_ID AND A.ADDRESS_SEQ_NUM = S.ADDRESS_SEQ_NUM AND S.SHIP_TO_ADDR = 'Y' AND A.EFF_STATUS = 'A';

DECLARE @column_name varchar(200)
DECLARE @column_value varchar(200)
DECLARE @SQLQuery AS NVARCHAR(500)
DECLARE @getRecordRef CURSOR
SET @getRecordRef = CURSOR FOR
SELECT column_name,column_value
FROM tx_reference_values
where record_type = 'tx_150erticswksite'
OPEN @getRecordRef
FETCH NEXT
FROM @getRecordRef INTO @column_name,@column_value
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQLQuery = 'update [tx_150ERTICSWKSITE] set '+ @column_name +'='''+ @column_value+''' where finalized=0'
print @SQLQuery
EXECUTE(@SQLQuery)
FETCH NEXT
FROM @getRecordRef INTO @column_name,@column_value
END
CLOSE @getRecordRef
DEALLOCATE @getRecordRef
END